#! /usr/bin/env python
# -*- coding: utf-8 -*-
import sys

import sqlite3,datetime,time
from vavava import util,testtools
import threading
_script = """
CREATE TABLE IF NOT EXISTS url(
id integer primary key,
desc TEXT,
url varchar(512),
track_time real,
category_id int,
create_time real
);
CREATE TABLE IF NOT EXISTS category (
id integer,
desc TEXT
);
"""

class DBRowUrl:
    def __init__(self,desc="",url="",track_time=time.time(),category=0):
        if url == "":
            return
        self.id = -1
        self.desc = desc
        self.url = url
        self.track_time = time.mktime(track_time)
        self.category_id = category
        self.create_time = time.time()

class DBUrl:
    LOCK = threading.RLock()
    TABLE_CREATED = False
    def __init__(self, path="tmp.db3",logger=None):
        self.__logger = util.LogAdapter(logger)
        self.db_path = path
        self.__conn = self.get_conn()
        self.create_db(_script)
        self.__initialized = True

    def get_conn(self):
        DBUrl.LOCK.acquire()
        try:
            re = self.__get_conn()
        except Exception as e:
            self.__logger.exception(e)
        finally:
            DBUrl.LOCK.release()
        return re
    def create_db(self,script):
        DBUrl.LOCK.acquire()
        try:
            if DBUrl.TABLE_CREATED: return
            self.__conn.executescript(script)
        except Exception as e:
            self.__logger.exception(e)
        finally:
            DBUrl.LOCK.release()
    def insert(self,url):
        DBUrl.LOCK.acquire()
        try:
            re = self.__insert(url)
        except Exception as e:
            self.__logger.exception(e)
        finally:
            DBUrl.LOCK.release()
        return re
    def query_by_desc(self,key=None,catagorys=[],page_num=1,page_size=10):
        DBUrl.LOCK.acquire()
        re = None
        try:
            re = self.__query_by_desc( key,catagorys,page_num,page_size )
        except Exception as e:
            self.__logger.exception(e)
        finally:
            DBUrl.LOCK.release()
        return re

    def __get_conn(self):
        try:
            self.__logger.info(self.db_path)
            self.__conn = sqlite3.connect(self.db_path,timeout=3)
            #self.__conn.text_factory = lambda x: unicode(x, "utf-8", "ignore")
            self.__initialized = True
            return self.__conn
        except Exception as e:
            self.__logger.error("failed on connect to db(%s).",self.db_path)
            self.__logger.exception(e)
    def __insert(self,url):
        if self.__initialized is None:
            return
        try:
            already_in_db = self.__query_by_url(url.url)
            if already_in_db and len(already_in_db)>0:
                self.__logger.debug("already in db")
                return False
            sql = "insert into url(desc,url,track_time,category_id,create_time) "\
                  "values('%s','%s',%f,%d,%f) "\
                  %(url.desc,url.url,url.track_time,url.category_id,url.create_time)
            #sql = unicode(sql)
            self.__conn.execute(sql)
            self.__conn.commit()
            return True
        except Exception as e:
            self.__logger.exception(e)
            return False
    def __query_by_desc(self,key,catagorys,page_num,page_size):
        if key:
            key = unicode(key,"gbk")
            key = key.strip()
        # init query sql
        catagory_condition = ""
        tmp_catagorys = []
        for i in range(len(catagorys)):
            tmp = catagorys[i]
            if isinstance(tmp,type("str")) and tmp.strip() != "":
                tmp_catagorys.append(int(tmp))
        if len(tmp_catagorys) > 0:
            for i in range(len(tmp_catagorys)):
                catagory = tmp_catagorys[i]
                if i > 0:
                    catagory_condition += " or category_id=%d "%catagory
                else:
                    catagory_condition += " and ( category_id=%d "%catagory
            catagory_condition += " ) "
        if key and key != "":
            sql1 = r"select count(id) from url "\
                  r"where desc like '%%%s%%' %s"%(key,catagory_condition)
            sql2 = r"select * from url "\
                  r"where desc like '%%%s%%' %s"\
                  r"order by track_time desc "%(key,catagory_condition)
        else:
            sql1 = r"select count(id) from url "
            sql2 = r"select * from url order by track_time desc "
        cursor = None
        try:
            cursor = self.__conn.cursor()
            cursor.execute(sql1)
            self.__logger.error("sql1=%s",sql1)
            dbset = cursor.fetchone()
             #data list size
            if dbset > 0:
                total = int(dbset[0])
            else:
                total = 0
            if page_size and page_size > 0:
                page_num = int(page_num)
                page_size = int(page_size)
                if page_size > 0:
                    sql2 += r" limit %d Offset %d"%(page_size,page_size*page_num)
            self.__logger.debug("quary(total=%d):%s",total,sql2)
            cursor.execute(sql2)
            dbset = cursor.fetchall()
            re = []
            for row in dbset:
                item = DBRowUrl()
                item.id          = row[0]
                item.desc        = row[1]
                item.url         = row[2]
                item.track_time  = time.localtime(row[3])
                item.category_id = row[4]
                item.create_time = time.localtime(row[5])
                re.append( item )
            cursor.close()
            return re,total
        except Exception as e:
            if cursor: cursor.close()
            self.__logger.exception(e)
    def __query_by_url(self,url):
        cursor = None
        try:
            cursor = self.__conn.cursor()
            sql = r"select * from url where url='%s'" % url
            cursor.execute(sql)
            dbset = cursor.fetchall()
            re = []
            for row in dbset:
                item = DBRowUrl()
                item.id          = row[0]
                item.desc        = row[1]
                item.url         = row[2]
                item.track_time  = time.localtime(row[3])
                item.category_id = row[4]
                item.create_time = time.localtime(row[5])
                re.append( item )
            cursor.close()
            return re
        except Exception as e:
            if cursor: cursor.close()
            self.__logger.exception(e)
    def __get_cagagoriy(self):
        cursor = None
        try:
            cursor = self.__conn.cursor()
            sql = r"select distinct id from url order by id"
            cursor.execute(sql)
            dbset = cursor.fetchall()
            re = []
            for row in dbset:
                re.append( row[0] )
            cursor.close()
            return re
        except Exception as e:
            if cursor: cursor.close()
            self.__logger.exception(e)

    def __del__(self):
        if self.__conn:
            self.__conn.close()

def test():
    db = DBUrl()
    if False:
        for i in range(100):
            db.insert(DBRowUrl(desc="desc",url="url_%d"%i))
    #db_set = db.query_by_desc("䶮")
    #db_set = db.query_by_id()
    bg=time.clock()
    db_set,total = db.query_by_desc()
    print "%s results ( about:%fs)"%(total, time.clock()-bg)
    for url in db_set:
        print "[%04d][%s][%10s]"%(url.id,time.strftime("%Y-%m-%d %H:%M:%S",url.track_time),url.desc), url.url

if __name__ == "__main__":
    test()


